/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package pe.edu.pucp.Reportes.DAO;

import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import pe.edu.pucp.Modelo.Aeropuerto;
import pe.edu.pucp.Modelo.Ciudad;
import pe.edu.pucp.Modelo.Continente;
import pe.edu.pucp.Modelo.Pais;
import pe.edu.pucp.Util.Constante;
import pe.edu.pucp.Util.HibernateUtil;
import pe.edu.pucp.Util.Reportclass.RepCancelaciones;

/**
 *
 * @author jorge
 */
public class ReporteCancelacionDAO {
    
    public ArrayList<Continente> listarContinente(){
        ArrayList<Continente> listaContinente = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = session.beginTransaction();
                    
        String q = "select ct from Continente ct order by ct.idcontinente";    
        
        Query query = session.createQuery(q);    
        List lista = query.list();
        Iterator iter = lista.iterator(); 
        while (iter.hasNext()) {    
            Continente a = (Continente) iter.next();
            listaContinente.add(a);
        }
        
        tx.commit();
        session.close();

        return listaContinente;
    }      
            
   
    public ArrayList<Pais> listarPais(int idcont){
        ArrayList<Pais> listaPais = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = session.beginTransaction();
        
        String q = "select p from Pais p where p.continente.idcontinente = :id order by p.idpais";       
        
        Query query = session.createQuery(q);       
        query.setInteger("id", idcont);
        List lista = query.list();
        Iterator iter = lista.iterator();        
        while (iter.hasNext()) {    
            Pais a = (Pais) iter.next();
            Hibernate.initialize(a.getContinente());
            listaPais.add(a);
        }
        
        tx.commit();
        session.close();

        return listaPais;
    }       
    
    public ArrayList<Ciudad> listarCiudad(int idpa){
        ArrayList<Ciudad> listaCiudad = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = session.beginTransaction();

        String q = "select c from Ciudad c where c.pais.idpais = :id order by c.idciudad";         
        
        Query query = session.createQuery(q);          
        query.setInteger("id", idpa);
        List lista = query.list();        
        Iterator iter = lista.iterator(); 
        while (iter.hasNext()) {    
            Ciudad a = (Ciudad) iter.next();
            Hibernate.initialize(a.getPais());
            Hibernate.initialize(a.getPais().getContinente());
            listaCiudad.add(a);
        }
        
        tx.commit();
        session.close();

        return listaCiudad;
    }    
    
    public ArrayList<Aeropuerto> listarAeropuerto(int idciu){
        ArrayList<Aeropuerto> listaAeropuerto = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = session.beginTransaction();
        
        String q = "select a from Aeropuerto a where a.ciudad.idciudad = :id order by a.idaeropuerto";                
        
        Query query = session.createQuery(q);        
        query.setInteger("id", idciu);
        List lista = query.list();        
        Iterator iter = lista.iterator(); 
        while (iter.hasNext()) {    
            Aeropuerto a = (Aeropuerto) iter.next();
            Hibernate.initialize(a.getCiudad());
            Hibernate.initialize(a.getCiudad().getPais());
            Hibernate.initialize(a.getCiudad().getPais().getContinente());
            listaAeropuerto.add(a);
        }
        
        tx.commit();
        session.close();

        return listaAeropuerto;
    }   
    
    public ArrayList<RepCancelaciones> listarCancelacion(int conto,int paiso,int ciudo,int contd,int paisd,int ciudd, Date inicio, Date fin){     
        ArrayList<RepCancelaciones> listaCancelacion = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = session.beginTransaction();        
              
        String q = "select s.origen, s.destino, cast(sum(s.sistema) as int) as sum1, cast(sum(s.cliente) as int) as sum2, cast(sum(s.pagado) as int) as sum3 "
            + "from (select ao.descripcion as origen, ad.descripcion as destino, "
            + "case when e.idestado=:estcan then count(e.idenvio) else 0 end as sistema, "
            + "case when e.idestado=:estcli then count(e.idenvio) else 0 end as cliente, "
            + "case when e.idestado=:estpag then count(e.idenvio) else 0 end as pagado "
            + "from envio e, aeropuerto ad, ciudad cd, pais pd, continente ctd, usuario u, "
            + "aeropuerto ao, ciudad co, pais po, continente cto "
            + "where ad.idaeropuerto = e.idaeropuerto and cd.idciudad = ad.idciudad "
            + "and pd.idpais = cd.idpais and ctd.idcontinente = pd.idcontinente "                
            + "and u.idusuario = e.idusuario and ao.idaeropuerto = u.idaeropuerto "
            + "and co.idciudad = ao.idciudad and po.idpais = co.idpais "
            + "and cto.idcontinente = po.idcontinente and e.idestado in (:estcan,:estcli,:estpag) ";       
        if (conto>0){ q+="and cto.idcontinente = :conto "; }
        if (paiso>0){ q+="and po.idpais = :paiso "; }
        if (ciudo>0){ q+="and co.idciudad = :ciudo "; }
        if (contd>0){ q+="and ctd.idcontinente = :contd "; }
        if (paisd>0){ q+="and pd.idpais = :paisd "; }
        if (ciudd>0){ q+="and cd.idciudad = :ciudd "; }
        if (inicio!=null){ q+="and cast(e.fechareg as date) >= cast(:dini as date) "; }
        if (fin!=null){ q+="and cast(e.fechareg as date) <= cast(:dfin as date) "; }        
        q+= "group by e.idestado, ao.descripcion, ad.descripcion " 
            + "order by ao.descripcion, ad.descripcion) s "
            + "group by s.origen,s.destino ";
        
        
        Query query = session.createSQLQuery(q);  
        query.setInteger("estpag", Constante.ESTADO_ENVIO_PAGADO);
        query.setInteger("estcan", Constante.ESTADO_ENVIO_CANCELADO);
        query.setInteger("estcli", Constante.ESTADO_ENVIO_CANCELADO_CLIENTE);
        if (conto>0){ query.setInteger("conto", conto); }                
        if (paiso>0){ query.setInteger("paiso", paiso); }
        if (ciudo>0){ query.setInteger("ciudo", ciudo); }
        if (contd>0){ query.setInteger("contd", contd); }                
        if (paisd>0){ query.setInteger("paisd", paisd); }
        if (ciudd>0){ query.setInteger("ciudd", ciudd); }
        if (inicio!=null){ query.setDate("dini", inicio); }
        if (fin!=null){ query.setDate("dfin", fin); }             
        List lista = query.list();        

        Iterator iter = lista.iterator(); 
        while (iter.hasNext()) {    
            Object[] row = (Object[]) iter.next();            
            if (row!=null){
                RepCancelaciones rep = new RepCancelaciones((String)row[0],(String)row[1],(int)row[2],(int)row[3],(int)row[3]);              
                listaCancelacion.add(rep);
            }
        }

        tx.commit();
        session.close();
        
        return listaCancelacion;
    }    
    
    public ArrayList<RepCancelaciones> listarCancelacion(){     
        ArrayList<RepCancelaciones> listaCancelacion = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = session.beginTransaction();                    
       
        String q = "select s.origen, s.destino, cast(sum(s.sistema) as int) as sum1, cast(sum(s.cliente) as int) as sum2 , cast(sum(s.pagado) as int) as sum3 "
            + "from (select ao.descripcion as origen, ad.descripcion as destino, "
            + "case when e.idestado=:estcan then count(e.idenvio) else 0 end as sistema, "
            + "case when e.idestado=:estcli then count(e.idenvio) else 0 end as cliente, "
            + "case when e.idestado=:estpag then count(e.idenvio) else 0 end as pagado "
            + "from envio e, aeropuerto ad, ciudad cd, pais pd, continente ctd, usuario u, "
            + "aeropuerto ao, ciudad co, pais po, continente cto "
            + "where ad.idaeropuerto = e.idaeropuerto and cd.idciudad = ad.idciudad "
            + "and pd.idpais = cd.idpais and ctd.idcontinente = pd.idcontinente "                
            + "and u.idusuario = e.idusuario and ao.idaeropuerto = u.idaeropuerto "
            + "and co.idciudad = ao.idciudad and po.idpais = co.idpais "
            + "and cto.idcontinente = po.idcontinente and e.idestado in (:estcan,:estcli,:estpag) " 
            + "group by e.idestado, ao.descripcion, ad.descripcion " 
            + "order by ao.descripcion, ad.descripcion) s "
            + "group by s.origen,s.destino ";

        Query query = session.createSQLQuery(q);          
        query.setInteger("estcan", Constante.ESTADO_ENVIO_CANCELADO);
        query.setInteger("estcli", Constante.ESTADO_ENVIO_CANCELADO_CLIENTE);
        query.setInteger("estpag", Constante.ESTADO_ENVIO_PAGADO);
        List lista = query.list();        

        Iterator iter = lista.iterator(); 
        while (iter.hasNext()) {    
            Object[] row = (Object[]) iter.next();            
            if (row!=null){
                RepCancelaciones rep = new RepCancelaciones((String)row[0],(String)row[1],(int)row[2],(int)row[3],(int)row[4]);              
                listaCancelacion.add(rep);
            }
        }

        tx.commit();
        session.close();
        
        return listaCancelacion;
    }
       
}
